/**
 *
 */
package com.ce.pms.house.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.util.StringAnalysiUtil;
import com.ce.pms.house.dao.ICoalitionHouseDao;
import com.ce.pms.house.model.CoalitionHouseDomain;
import com.ce.pms.house.model.FlatDomain;
import com.ce.pms.house.model.HouseDomain;
import com.ce.util.BlankUtil;

/**
 * 功能描述: CoalitionHouseDaoImpl
 * <p>
 * 版权所有：中企动力
 * <p>
 * 未经本公司许可，不得以任何方式复制或使用本程序任何部分
 *
 * @author 付湘辉
 * Created on： 2008-7-1
 * @author
 * update on：
 */

public class CoalitionHouseDaoImpl extends PmsBaseDaoImpl<CoalitionHouseDomain>
		implements ICoalitionHouseDao {

	/**
	 * @param coalitionHouseDomain
	 * @roseuid 485A2B2A0203
	 */
	public void saveCoalitionHouse(CoalitionHouseDomain coalitionHouseDomain) {

	}

	/**
	 * @param coalitionHouseDomain
	 * @roseuid 485A2B2A0205
	 */
	public void updateCoalitionHouse(CoalitionHouseDomain coalitionHouseDomain) {

	}

	/**
	 * @param houseId
	 * @param houseList
	 * @roseuid 485A2B2A0213
	 */
	public void deleteCoalitonHouse(Long houseId, List houseList) {

	}

	/**
	 * @param houseId
	 * @return List<FlatDomain>
	 * @roseuid 485A2B2A0216
	 */
	public List<FlatDomain> findHouseByCoalitionId(Long houseId) {
		return null;
	}

	/**
	 * @param flatDomain
	 * @return List
	 * @roseuid 485A2B2A0222
	 */
	public List checkHouseIsUnited(List<FlatDomain> flatDomain) {
		return null;
	}

	/**
	 * 功能描述：检查管理房是否和其它的管理房包含了同样的产权房
	 * @author Adama Yue
	 * @param flatDomains
	 * @return Boolean
	 * @roseuid 485A2B2A0224
	 */
	 public Boolean checkHousesDuplicate(CoalitionHouseDomain coalition) {
		Boolean retValue = false;

		StringBuffer sql = new StringBuffer("SELECT m.MANAGE_ID FROM HM_REAL_R_VIRTUAL r INNER JOIN HM_MANAGE m ON r.VIRTUAL_HOUSE_ID=m.MANAGE_ID WHERE r.REAL_HOUSE_ID IN (");
		for(HouseDomain h:coalition.getHouses()) {
			sql.append(h.getKeyword()).append(",");
		}
		sql.setLength(sql.length()-1);
		sql.append(") AND m.VALIDITY=1");

		if(coalition.getKeyword()!=null) {
			//当修改时不检查自已原来包含的house
			sql.append(" AND m.MANAGE_ID <> ")
			.append(coalition.getKeyword());
		}


		if(createSQLQuery(sql.toString(),
				new Object[]{}).list().size()>0) {
			retValue = true;
		}
		 return retValue;
	}

	/**
	 *
	 * 功能描述: 检查是否存在指定房号的管理房
	 *
	 * @author 付湘辉
	 * Created on:2008-7-1
	 *
	 * 修改原因：
	 * @param coalitonNumber
	 * @roseuid 485A2B2A0226
	 * @return Boolean true 存在　false 不存在
	 */
	public Boolean checkUnitedHouseNumberExist(String coalitonNumber) {
		return this.getCoalitionHouseByHouseNumber(coalitonNumber)!=null;
	}

	/**
	 *
	 * 功能描述: 检查在指定期数下是否存在指定房号的管理房
	 *
	 * @author 付湘辉
	 * Created on:2008-7-1
	 *
	 * 修改原因：
	 * @param coalitonNumber
	 * @roseuid 485A2B2A0226
	 * @return Boolean true 存在　false 不存在
	 */
	public Boolean checkUnitedHouseNumberExist(Long projectId,String coalitonNumber) {
		return this.getCoalitionHouseBy(projectId,coalitonNumber)!=null;
	}

	/**
	 *
	 * 功能描述: 检查是否存在除给定id外指定房号的管理房
	 *
	 * @author Adama yue
	 * Created on:2008-7-1
	 *
	 * 修改原因：当修改管理房的时候必需排除当前的管理房再验证唯一性
	 * @param coalitonNumber
	 * @roseuid 485A2B2A0226
	 * @return Boolean 返回结果
	 */
	public Boolean checkUnitedHouseNumberExist(String coalitonNumber,Long id) {

		Boolean retValue = false;
		//与其它管理房比较
		StringBuffer sql = new StringBuffer("SELECT h.HOUSE_ID FROM HM_HOUSE h INNER JOIN HM_MANAGE m ON h.HOUSE_ID = m.MANAGE_ID WHERE h.HOUSE_NUMBER ='")
		.append(StringAnalysiUtil.toTropicalString(coalitonNumber,"'"))
		.append("' AND h.IS_VIRTUAL=1 AND m.VALIDITY=1 AND h.HOUSE_ID<>")
		.append(id);

		if(createSQLQuery(sql.toString()).list().size()>0) {
			retValue = true;
		}
		//与产权房比较
//		sql = new StringBuffer("SELECT h.HOUSE_ID FROM HM_HOUSE h WHERE h.IS_VIRTUAL=0 AND h.HOUSE_NUMBER='")
//					.append(coalitonNumber)
//					.append("'");
//		if(createSQLQuery(sql.toString(),
//				new Object[]{}).list().size()>0) {
//			retValue = true;
//		}
		 return retValue;
	}

	/**
	 *
	 * 功能描述: 根据管理房号查询管理房实体，如果不存在返回空值。
	 * 只查询有效的信息，
	 *
	 * @author 付湘辉
	 * Created on:2008-7-1
	 *
	 * 修改原因：
	 * @param houseNumber　管理房号
	 * @return CoalitionHouseDomain 返回结果
	 */
	public CoalitionHouseDomain getCoalitionHouseByHouseNumber(String houseNumber){
		CoalitionHouseDomain coalitionHouse = this.getCoalitionHouseBy(null, houseNumber);
		return coalitionHouse;
	}

	/**
	 *
	 * 功能描述: 根据管理房号和期数id查询管理房实体，如果不存在返回空值。
	 * 只查询有效的信息，
	 *
	 * @author 付湘辉
	 * Created on:2008-7-1
	 *
	 * 修改原因：
	 * @param houseNumber　管理房号
	 * @return CoalitionHouseDomain 返回结果
	 */
	private CoalitionHouseDomain getCoalitionHouseBy(Long projectId,String coalitionNumber){
		CoalitionHouseDomain coalitionHouse = null;
		List<Object> list = new ArrayList<Object>();
		int i=0;
		StringBuilder queryStr = new StringBuilder("FROM CoalitionHouseDomain ch WHERE ");
		if (!BlankUtil.isBlank(projectId)) {
			list.add(i++,projectId);
			queryStr.append("ch.building.project.id=? AND ");
		}
		list.add(i++,coalitionNumber);
		queryStr.append("ch.houseNumber=? AND ch.validity=1 ");
		Query query = super.createQuery(queryStr.toString(),list.toArray());
		List obj = query.list();
		if (BlankUtil.isBlank(obj)) {
			return null;
		}
		coalitionHouse = (CoalitionHouseDomain)obj.get(0);

		return coalitionHouse;
	}

	/**
	 * 跟据houses查询是否存在关联houses集合的管理房
	 *@param houses 房屋集合
	 * @author Adama Yue
	 * @return
	 * @CreateDate:下午04:16:27
	 */
	public CoalitionHouseDomain findOld(Set<HouseDomain> houses) {
		CoalitionHouseDomain coalitionHouse = null;

//		StringBuffer queryStr = new StringBuffer("SELECT HM_REAL_R_VIRTUAL.VIRTUAL_HOUSE_ID FROM HM_REAL_R_VIRTUAL INNER JOIN HM_MANAGE ON  HM_REAL_R_VIRTUAL.VIRTUAL_HOUSE_ID =HM_MANAGE.MANAGE_ID WHERE HM_MANAGE.VALIDITY=0 AND HM_REAL_R_VIRTUAL.REAL_HOUSE_ID IN(");
//		for(HouseDomain h:houses) {
//			queryStr.append(h.getKeyword()).append(",");
//		}
//		queryStr.setLength(queryStr.length()-1);
//		queryStr.append(")")
//			.append(" GROUP BY HM_REAL_R_VIRTUAL.VIRTUAL_HOUSE_ID HAVING COUNT(*) =")
//			.append(houses.size());

		StringBuffer queryStr = new StringBuffer("SELECT DISTINCT  TEMP1.VIRTUAL_HOUSE_ID FROM ");
		queryStr.append(" (SELECT VIRTUAL_HOUSE_ID FROM HM_REAL_R_VIRTUAL   INNER JOIN HM_MANAGE ON ")
		        .append(" HM_REAL_R_VIRTUAL.VIRTUAL_HOUSE_ID =HM_MANAGE.MANAGE_ID ")
		        .append(" WHERE  HM_MANAGE.VALIDITY=0 GROUP BY VIRTUAL_HOUSE_ID HAVING COUNT(*) = ")
		        .append(houses.size()).append(") ")
		        .append(" TEMP1 ,HM_REAL_R_VIRTUAL TEMP2 WHERE TEMP1.VIRTUAL_HOUSE_ID = TEMP2.VIRTUAL_HOUSE_ID")
		        .append(" AND REAL_HOUSE_ID IN (");
		for(HouseDomain h:houses) {
			queryStr.append(h.getKeyword()).append(",");
		}
		queryStr.setLength(queryStr.length()-1);
		queryStr.append(")");
		Query query = super.createSQLQuery(queryStr.toString(), new Object[]{});
		Object obj = query.uniqueResult();
		if (BlankUtil.isBlank(obj)) {
			return null;
		}
		coalitionHouse = (CoalitionHouseDomain)findById(((BigInteger)obj).longValue());
		return coalitionHouse;
	}

	/**
	 * 功能描述: 根据产权房号查询其所属管理房 <br>
	 * @param houseId
	 * @return
	 * Create author:yandawei <br>
	 * Create on:2008-9-8  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public Object getCoalitionHouseByHouseId(Long houseId)
	{
		String hql = "SELECT virtual_house_id,real_house_id,validity,manage_id FROM HM_MANAGE Inner Join HM_REAL_R_VIRTUAL ON manage_id = virtual_house_id WHERE validity = '1' AND real_house_id = ? ";
		Query query = super.createSQLQuery(hql, new Object[]{houseId});
		Object obj = query.uniqueResult();
		return obj;
	}
}
